package com.springboot.erp.mapper;

import com.springboot.erp.entity.Stock_statistics;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Component;

import java.util.List;

@Mapper
@Component
public interface Stock_statisticsMapper {
    //获取stock_statistics表中所有信息(分页)
    @Select("select index_no,warehouse_no,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks from stock_statistics limit #{start},#{count}")
    List<Stock_statistics> get_All_Stock_statistics_ByPage(int start,int count);

    @Select("select index_no,warehouse_no,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks from stock_statistics")
    List<Stock_statistics> get_All_Stock_statistics();

    //获取stock_statistics表中一共有多少条记录
    @Select("select count(*) from stock_statistics")
    int getRecordCount();

    /*****修改指定物品的数量******/
    @Update("update stock_statistics set count=#{count},total_price=#{count}*single_price where goods_no=#{goods_no}")
    void renew_goods_no_count(int count,String goods_no);

    @Select("delete from stock_statistics where goods_no = #{goods_no}")
    void delete_by_goods_no(String goods_no);

    @Select("delete from stock_statistics where index_no = #{index_no}")
    void delete_by_index_no(String index_no);

   /* @Update("update stock_statistics set count=count+#{morecount} and total_price=count*single_price where goods_no=#{goods_no}")
    void renew_goods_no_count_add(int morecount,String goods_no);*/

    @Insert("insert into stock_statistics (index_no,warehouse_no,list_no,goods_no,kind,specification," +
            "goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks) " +
            "values (#{index_no},#{warehouse_no},#{list_no},#{goods_no},#{kind},#{specification},#{goods_name},#{single_price},#{count},#{total_price}," +
            "#{manufacturer},#{entry_date},#{out_date},#{status},#{remarks})")
    void add_stock_statistics_items(Stock_statistics stock_statistics);

    @Update("update stock_statistics set count=count+1 and total_price=count*single_price where goods_no=#{goods_no}")
    void update_add_stock_statistics_items_count_if_exist(String goods_no);

    @Update("update stock_statistics set count=count-1 and total_price=count*single_price where goods_no=#{goods_no}")
    void update_minus_stock_statistics_items_count_if_exist(String goods_no);

    //获取index_no
    @Select("select nextval('seq_stock_statistics_index_no')")
    String get_seq_stock_statistics_index_no();

    //获取指定的goods_no的库存余额信息
    @Select("select index_no,warehouse_no,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks from stock_statistics where goods_no=#{goods_no}")
    Stock_statistics get_by_goods_no(String goods_no);

    //根据指定的index_no获取库存信息
    @Select("select index_no,warehouse_no,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks from stock_statistics where index_no=#{index_no}")
    Stock_statistics get_by_index_no(String index_no);



    //修改指定的index_no的库存统计信息
    @Update("update stock_statistics set warehouse_no=#{warehouse_no},goods_no=#{goods_no},kind=#{kind}," +
            " specification=#{specification},goods_name=#{goods_name},single_price=#{single_price},count=#{count}," +
            "total_price=count*single_price," +
            "remarks=#{remarks} where index_no=#{index_no}")
    void update_stock_statistics_by_index_no(String index_no,String warehouse_no,String goods_no,String kind,String specification,String goods_name,
                                             double single_price,int count,double total_price,String remarks);

    //获取指定仓库中所有物品的信息
    @Select("select index_no,warehouse_no,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks from stock_statistics where warehouse_no=#{warehouse_no}")
    List<Stock_statistics> get_by_warehouse_no(String warehouse_no);

    //修改库存状态（过少，正常，过多）
    @Update("update stock_statistics set status=#{status} where warehouse_no=#{warehouse_no} and goods_no=#{goods_no}")
    void update_status(String status,String warehouse_no,String goods_no);

    //根据warehouse_no和goods_no获取库存信息
    @Select("select index_no,warehouse_no,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer," +
            "entry_date,out_date,status,remarks from stock_statistics where warehouse_no=#{warehouse_no} and goods_no=#{goods_no}")
    Stock_statistics get_by_warehouse_no_goods_no(String warehouse_no,String goods_no);

   /* //获取表warehouse_remain中所有信息
    @Select("select warehouse_no,list_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks from warehouse_remain")
    List<Stock_statistics> get_All_Warehouse_remain();

    //获取表warehouse_remain的记录数
    @Select("select count(*) from warehouse_remain")
    int getRecordCount();

    //分页显示所有库存余额信息
    @Select("select warehouse_no,goods_no,kind,specification,goods_name,single_price,count,total_price,remarks from warehouse_remain limit #{start},#{count}")
    List<Stock_statistics> get_All_Warehouse_remain_ByPage(int start,int count);

    //根据入库信息和出库信息修改库存余额
    @Update("update warehouse_remain set count=#{count} where goods_no=#{goods_no}")
    void renew_goods_no_count(int count,String goods_no);

    //根据goods_no获取库存余额信息
    @Select("select warehouse_no,goods_no,kind,specification,goods_name,single_price,count,total_price,remarks from warehouse_remain where goods_no=#{goods_no}")
    Stock_statistics get_warehouse_remain_by_goods_no(String goods_no);*/


 /*   //根据出库信息修改库存余额
    @Update("update warehouse_remain set count=count-#{lesscount} where goods_no=#{goods_no}")
    void renew_goods_no_count_minus(int lesscount,String goods_no);*/

    //根据仓库编号显示
   // @Select("select warehouse_no,goods_no,kind,specification,goods_name,single_price,count,total_price,manufacturer,entry_date,out_date,status,remarks from warehouse_remain limit #{start},#{count}")






}
